var express = require('express');
var fs = require('fs');
var path = require('path');
var router = express.Router();
var session = require('express-session');
router.use(session({secret: 'keyboard cat', resave: false, saveUninitialized: true, cookie: {maxAge: 60000}}));

// 导入MySQL模块
var mysql = require('mysql');

var dbConfig =  {  
    mysql: {   
        host: '192.168.2.200',
        user: 'root',
        port: '4406',
        password : '123456',
        database : 'wechat'
    }
 }

var blogSQL = {
    //查询博客下的评论条数和点赞条数
    findAllBlogAndCommit:'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog order by creat_time DESC LIMIT ?,?' ,
    findAllBlog:'SELECT `blog`.* ,`blog_class`.className FROM `blog` LEFT JOIN `blog_class` ON `blog`.blogId = `blog_class`.blogId',//多表关联查询 左连接 查询包含每条博客的标签
    findAllBlogByclassName:'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog where blog.showIndex = 1 and className = ?order by creat_time DESC LIMIT ?,?',
    addBlog:'INSERT INTO blog(userName,title,body,className,creat_time,update_time) VALUES(?,?,?,?,?,?)',
    updateBlog:'UPDATE blog SET title = ?,body = ?,className=?,update_time=? WHERE blogId = ?',
    deleteBlog:'DELETE FROM blog WHERE blogId = ?',
    findBlogByUsername:'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog WHERE userName = ? and blog.showIndex = 1 order by update_time DESC',
    addBlogClass:'UPDATE blog SET className = ? WHERE blogId = ?',
    findBlogClass:'SELECT DISTINCT className FROM `blog` where showIndex = 1',
    findBlogByClassName:'SELECT * FROM blog WHERE className = ?',
    findBlogCommitByBlogId:'SELECT * from user as a LEFT JOIN blog_commit as b on a.name = b.commitName where blogId = ? order by update_time DESC',
    findBlogByBlogId:'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog where blogId = ?',
    addCommit:'INSERT INTO blog_commit(commitName,blogId,commitBody,creat_time,avatar) VALUES (?,?,?,?,?)',
    changeHeadImg:'UPDATE user SET headImg = ? WHERE `user`.name = ? and `user`.`password`= ?',
    //查找所有博客评论条数 :'select blog.* , (select count(id) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCountfrom blogorder by blog.blogId)'
    updatedUserInfo:'UPDATE user SET nickName = ?,info = ?,sex = ? WHERE name= ? and password = ?',
    findFabByUsername:'select * from blog as a LEFT JOIN blog_fabulous as b on a.blogId = b.blogId where commitName = ?'
}   

var pool = mysql.createPool( dbConfig.mysql );

//查询博客
router.post('/findAllBlogAndCommit', function(req, res, next){ //username
    var param = req.body
    var row =  parseInt(param.row) 
    var page = (param.page-1) * row
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.findAllBlogAndCommit,[page,row],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'查询失败'});
                connection.release()
            }else{
                result.forEach(item=>{
                    connection.query('SELECT * from blogImg where blogId = ?',[item.blogId],function (err,blogImg) {
                        item.blogImg = []
                        blogImg.forEach(items=>{
                            item.blogImg.push(items.blogImg)
                        })
                       
                    })
                    connection.query('SELECT * FROM `blog_commit` where blogId = ? ORDER BY love desc',[item.blogId],function (err,hot) {
                        item.hotCommit = hot[0]
                    })
                })
                connection.query('SELECT COUNT(blogId) from blog',function (err,result2) {
                    res.send({success:true,data:result,total:result2[0]['COUNT(blogId)']});
                    connection.release()
                })
            }
        })
    })
})

//增加点赞数 含有过滤用户 参数blogId userName
router.post('/addFabulous', function(req, res, next){ //username,参数blogId
    var param = req.body;//放到body里面
    pool.getConnection(function(err, connection) {
        connection.query('select blogId from blog_fabulous where commitName = ?',[param.userName],function (err,result) {
            console.log(result,result == false)
                var exit 
                result.forEach(item=>{
                    console.log(item.blogId)
                    if(item.blogId==param.blogId){
                        exit = true
                        
                    }else{
                        exit = false
                    }
                })
                if(!exit){
                    connection.query('INSERT INTO blog_fabulous(commitName,blogId) VALUES(?,?)',[param.userName,param.blogId],function (err,result) {
                        if(err) {
                            res.send({success:false,data:err,msg:'点赞失败2'})
                            connection.release()
                        }
                        else {
                            res.send({success:true,data:'点赞成功!'});
                            connection.release()
                        }
                    })
                }else{
                    res.send({success:false,data:err,msg:'已经点赞过了,不要重复点赞'});
                    connection.release()
                }
        })
    })
})

//增加点赞数 含有过滤用户 参数blogId userName
router.post('/addMsgF', function(req, res, next){ //commitName,blogId,commitBody,avatar
    var param = req.body;
    console.log(param, req.body)
    pool.getConnection(function(err, connection) {
        connection.query('UPDATE message SET love = love + 1 WHERE id = ?',[param.id],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'点赞失败'});
                connection.release()
            }else{
                res.send({success:true,data:'点赞成功'});
                connection.release()
            }
        })
    })
})

//增加评论
router.post('/addCommit', function(req, res, next){ //commitName,blogId,commitBody,avatar
    var param = req.body;
    console.log(param, req.body)
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.addCommit,[param.commitName,param.blogId,param.commitBody,new Date(),param.avatar],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'评论失败'});
                connection.release()
            }else{
                res.send({success:true,data:'评论成功'});
                connection.release()
            }
        })
    })
})

//增加祝福
router.post('/addBless', function(req, res, next){ //name,headImg
    var param = req.body;
    console.log(param, req.body)
    pool.getConnection(function(err, connection) {
        connection.query('select name from blessing',[param.name],function (err,result) {
                console.log(result,result == false)
                var exit 
                result.forEach(item=>{
                    console.log(item.blogId)
                    if(item.name==param.name){
                        exit = true
                    }else{
                        exit = false
                    }
                })
                if(!exit){
                    connection.query('INSERT INTO blessing(name,headImg,creat_time) VALUES (?,?,?)',[param.name,param.headImg,new Date()],function (err,result) {
                        if(err){
                            res.send({success:false,data:err,msg:'祝福失败'});
                            connection.release()
                        }else{
                            res.send({success:true,data:'祝福成功'});
                            connection.release()
                        }
                    })
                }else{
                    res.send({success:false,data:err,msg:'已经祝福过了,不要重复点赞'});
                    connection.release()
                }
        })
    })
})
//祝福查重接口
router.post('/getBlessByName', function(req, res, next){ //name
    var param = req.body;
    console.log(param, req.body)
    pool.getConnection(function(err, connection) {
        connection.query('select * from blessing where headImg = ?',[param.headImg],function (err,result) {
            res.send({success:true,data:result,msg:'祝福列表'});
        })
    })
})

//获取所有祝福
router.post('/getBless', function(req, res, next){ //name,headImg
    var param = req.body;
    pool.getConnection(function(err, connection) {
        connection.query('select * from blessing',function (err,result) {
                if(err){
                    res.send({success:false,data:err,msg:'祝福失败'});
                    connection.release()
                }else{
                    res.send({success:true,data:result});
                    connection.release()
                }
        })
    })
})


//增加留言
router.post('/addMsg', function(req, res, next){ //commitName,commitBody,avatar
    var param = req.body;
    console.log(param, req.body)
    pool.getConnection(function(err, connection) {
        connection.query('INSERT INTO message(name,headImg,body,creat_time) VALUES (?,?,?,?)',[param.commitName,param.avatar,param.commitBody,new Date()],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'评论失败'});
                connection.release()
            }else{
                res.send({success:true,data:'评论成功'});
                connection.release()
            }
        })
    })
})


//查询所有留言
router.post('/getMsg', function(req, res, next){ //name,headImg
    var param = req.body;
    pool.getConnection(function(err, connection) {
        connection.query('select * from message',function (err,result) {
                if(err){
                    res.send({success:false,data:err,msg:'查询失败,数据库错误'});
                    connection.release()
                }else{
                    res.send({success:true,data:result});
                    connection.release()
                }
        })
    })
})


//查询博客评论
router.post('/getBlogMsg', function(req, res, next){ //name,headImg
    var param = req.body;
    pool.getConnection(function(err, connection) {
        connection.query('select * from blog_commit where blogId = ?',[param.blogId],function (err,result) {
                if(err){
                    res.send({success:false,data:err,msg:'查询失败,数据库错误'});
                    connection.release()
                }else{
                    res.send({success:true,data:result});
                    connection.release()
                }
        })
    })
})

//取得服务器上面的图片
router.get('/wxImg/*', function (req, res) {
    //__dirname就是执行这行代码的地址
    //path.resolve实际上就是cd __dirname 然后再 cd ..
    console.log(req.url)
    res.sendFile(path.resolve(__dirname, '..') + req.url);
    console.log("Request for " + req.url + " received.");
})

//取得服务器上面的音乐
router.get('/music/*', function (req, res) {
    //__dirname就是执行这行代码的地址
    //path.resolve实际上就是cd __dirname 然后再 cd ..
    console.log(req.url)
    res.sendFile(path.resolve(__dirname, '..') + req.url);
    console.log("Request for " + req.url + " received.");
})

//取得服务器上面的音乐
router.get('/getMusic', function (req, res) {
    pool.getConnection(function(err, connection) {
        connection.query('select * from music',function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'查询失败,数据库错误'});
                connection.release()
            }else{
                res.send({success:true,data:result});
                connection.release()
            }
        })
    })
})



module.exports = router;
